package Reports;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class QuerySupplierDepart {

	 Connection conn = null;

	public QuerySupplierDepart( Connection conn) {
		this.conn= conn;
	}
	public   void getCommonData(String email,Map<String, List> map,List<String[]> arrylist,String sql){
		PreparedStatement pstat = null;
		try {
			pstat = conn.prepareStatement(sql);
			ResultSet rs = pstat.executeQuery();
			while (rs.next()) {
				String company = rs.getString(1);
				String code = rs.getString(2);
				String name = rs.getString(3);
				String[] arr = { company, code, name };
				arrylist.add(arr);
				map.put(email, arrylist);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			try {
				if (pstat != null)
					pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public Map<String, List> Query(Map<String, List> map) {
		String sql = "SELECT  DISTINCT TC.SCEMAIL from  CHYIII_DEC_TSUPPLIER TS,CHYIII_DEC_TSUPPLIERCONTACT  TC  where TS.SUPPLIERCODE = TC.SUPPLIERCODE and TC.SCEMAIL IN (SELECT TC.SCEMAIL from  CHYIII_DEC_TDEPARTMENT TD,CHYIII_DEC_TSUPPLIERCONTACT TC where TD.DEPARMENTTCODE = TC.DEPTCODE) ORDER BY TC.SCEMAIL";
		PreparedStatement pstat = null;
		List<String[]> arrylist = new ArrayList<String[]>();
		try {
			pstat = conn.prepareStatement(sql);
			ResultSet rs = pstat.executeQuery();
			while (rs.next()) {
				arrylist = new ArrayList<String[]>();
				String email = rs.getString(1);
				String sql2 = "SELECT TS.SUPPLIERNAME,TS.SUPPLIERCODE,TC.SCNAME  from  CHYIII_DEC_TSUPPLIER ts,CHYIII_DEC_TSUPPLIERCONTACT tc where ts.SUPPLIERCODE = TC.SUPPLIERCODE and TC.SCEMAIL='"
					+ email + "'";
				String sql3 = "SELECT TD.DEPARTMENTNAME,TD.DEPARMENTTCODE,TC.SCNAME,TC.SCEMAIL from  CHYIII_DEC_TDEPARTMENT TD,CHYIII_DEC_TSUPPLIERCONTACT TC where TD.DEPARMENTTCODE = TC.DEPTCODE and  TC.SCEMAIL='"
					+ email + "'";
				getCommonData(email, map, arrylist, sql2);
				getCommonData(email, map, arrylist, sql3);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			try {
				if (pstat != null)
					pstat.close();
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}
}
